package com.pennphoto.server;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import com.pennphoto.client.Circles;
import com.pennphoto.client.QueryService;
import com.pennphoto.client.ViewedPhotos;
import com.pennphoto.client.ViewedType;
import com.pennphoto.shared.PennPhotoConstants;


@SuppressWarnings("serial")
public class QueryServiceImpl extends RemoteServiceServlet implements QueryService {
	public static QueryServiceImpl engine;
	public Connection conn;
 
	
	public QueryServiceImpl() throws Exception
	{
		 Class.forName ("oracle.jdbc.OracleDriver");

	     conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
	     
	}
	
	public int getUserIdForCredentials(String username, String passwd) throws Exception
	{
		Class.forName ("oracle.jdbc.OracleDriver");

	    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.dbusername,PennPhotoConstants.dbpwd);
		PreparedStatement pstmt = conn.prepareStatement("select userid from "+PennPhotoConstants.schemaName+"users where email=? and passwd=?");
		pstmt.setString(1, username);
		pstmt.setString(2, passwd);
		ResultSet rset = pstmt.executeQuery();
		int userid=0;
		while(rset.next())
			userid=rset.getInt(1);
		rset.close();
		pstmt.close();
		//conn.close();
		return userid;
	}
	
	public com.pennphoto.client.PersonType getUserInformation(Integer userId) throws Exception
	{
		com.pennphoto.client.ObjectFactory factory = new com.pennphoto.client.ObjectFactory();
		com.pennphoto.client.PersonType person = factory.createPersonType();
		 PreparedStatement pstmt = conn.prepareStatement("SELECT firstname,lastname,dob,gender,email,address FROM "+PennPhotoConstants.schemaName+"users  WHERE userid=?");
		 pstmt.setInt(1,userId);
		 ResultSet rset = pstmt.executeQuery();
		 while(rset.next())
		 {
			 String fname=rset.getString(1);
			 String lname=rset.getString(2);
			 person.setName(fname+" "+lname);
			 String gender = rset.getString(4);
			 String email = rset.getString(5);
			 person.setGender(gender);
			 person.setEmail(email);
			 person.setUserId(userId);
		 }
		 rset.close();
			pstmt.close();
			//conn.close();
		String result= "<table><tr><td>Name:</td><td>"+person.getName()+"</td></tr>"+"<tr><td>gender:</td><td>"+person.getGender()+"</td></tr>"+"<tr><td>email:</td><td>"+person.getEmail()+"</td></tr></table>";
		 return person;
	}
	

	
	public static QueryServiceImpl getInstance() throws Exception
	{
		if(engine==null)
			engine = new QueryServiceImpl();
		return engine;
	}

	@Override
	public ArrayList<ViewedPhotos> getUserPhotos(int userId) throws Exception {
		CallableStatement proc = conn.prepareCall("BEGIN "+PennPhotoConstants.schemaName+"updateScore(?); END;");
		proc.setInt(1, userId);
		proc.executeUpdate();
		
		
		com.pennphoto.client.ObjectFactory factory = new com.pennphoto.client.ObjectFactory();
		 ArrayList<ViewedType> views =  new ArrayList<ViewedType>();
		// PreparedStatement pstmt = conn.prepareStatement("SELECT viewerid, photoid,rate,comments FROM "+PennPhotoConstants.schemaName+"viewed WHERE photoid in (SELECT photoid FROM (SELECT photoid, DENSE_RANK() OVER (ORDER BY score) relrank FROM "+PennPhotoConstants.schemaName+"photos WHERE uploadedby= ?) WHERE relrank<=5)", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		 PreparedStatement pstmt = conn.prepareStatement("SELECT viewerid, photoid,rate,comments FROM "+PennPhotoConstants.schemaName+"viewed WHERE photoid in (SELECT photoid FROM (SELECT photoid, DENSE_RANK() OVER (ORDER BY score) relrank FROM "+PennPhotoConstants.schemaName+"photos WHERE photoid IN (SELECT photoid FROM "+PennPhotoConstants.schemaName+"visiblity WHERE viewerid= ?)) WHERE relrank<=10)", ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		 pstmt.setInt(1,userId);
		 ResultSet rset = pstmt.executeQuery();
		ArrayList<Integer> pids =new ArrayList<Integer>();
		ArrayList<Integer> vids=new ArrayList<Integer>();
		ArrayList<ViewedPhotos> vieweds = new ArrayList<ViewedPhotos>();
		int rowCount=0;
		if (rset.last()) {
			rowCount = rset.getRow();
			  rset.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
		}
		if(rowCount!=0)
		{
		 while(rset.next())
		 {
			 ViewedType view = factory.createViewedType();
			 int vid = rset.getInt(1);
			 int pid = rset.getInt(2);
			 float rate = rset.getFloat(3);
			 pids.add(pid);
			 String comments=rset.getString(4);
			
			 vids.add(vid);
			 view.setViewerId(vid);
			 view.setRate(rate);
			 view.setComment(comments);
			 view.setPhotoId(pid);
			 views.add(view);
			 
		 }
		 
		
		 HashMap<Integer,String> urls = new HashMap<Integer,String>();
		 String st2="SELECT photoid,url FROM "+PennPhotoConstants.schemaName+"photos WHERE photoid in (%s)";
		 st2=String.format(st2,preparePlaceHolders(pids.size()));
		 PreparedStatement pstmt2 = conn.prepareStatement(st2);
		 for (int i = 0; i < pids.size(); i++) 
			 pstmt2.setObject(i + 1, pids.get(i));
			 
		 ResultSet rset2 = pstmt2.executeQuery();
		 while (rset2.next())
		 {
			 urls.put(rset2.getInt(1),rset2.getString(2));
		 }
		 
		// vids=vids.substring(0, vids.lastIndexOf(","));
		 String st3="SELECT userid,firstname,lastname FROM "+PennPhotoConstants.schemaName+"users WHERE userid in (%s)";
		 st3=String.format(st3,preparePlaceHolders(vids.size()));
		 PreparedStatement pstmt3 = conn.prepareStatement(st3);
		 for (int i = 0; i < vids.size(); i++) 
			 pstmt3.setObject(i + 1, vids.get(i));
		 HashMap<Integer,String> viewers = new HashMap<Integer,String>();
		 ResultSet rset3 = pstmt3.executeQuery();
		 while (rset3.next())
		 {
			 int uid=rset3.getInt(1);
			 String name=rset3.getString(2)+" "+rset3.getString(3);
			 viewers.put(uid,name);
		 }
		
		//String result= "<table><tr><td>Name:</td><td>"+person.getName()+"</td></tr>"+"<tr><td>gender:</td><td>"+person.getGender()+"</td></tr>"+"<tr><td>email:</td><td>"+person.getEmail()+"</td></tr></table>";
			String result="<table>";
		
			for(int p:urls.keySet())
			{
				ViewedPhotos vp = new ViewedPhotos();
				vp.setPhotoid(p);
				vp.setUrl(urls.get(p));
				ArrayList<Integer> viewids = new ArrayList<Integer>();
				ArrayList<String> pviewers = new ArrayList<String>();
				ArrayList<String> comments = new ArrayList<String>();
				for(ViewedType v:views)
				{
					if(v.getPhotoId()==p)
					{
						if(viewers.containsKey(v.getViewerId()))
						{
							viewids.add(v.getViewerId());
							pviewers.add(viewers.get(v.getViewerId()));
							comments.add(v.getComment());
						}
						
					}
				}
				vp.setViewerids(viewids);
				vp.setViewers(pviewers);
				vp.setComments(comments);
				vieweds.add(vp);
			}
			result+="</table>";
			 rset.close();
			 rset2.close();
			 rset3.close();
				pstmt.close();
				pstmt2.close();
				pstmt3.close();
				//conn.close();
		}
		else{
			HashMap<Integer,String> urls = new HashMap<Integer,String>();			 			
			 PreparedStatement pstmt2 = conn.prepareStatement("SELECT photoid,url,description FROM "+PennPhotoConstants.schemaName+"photos WHERE uploadedby = ?",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
			 pstmt2.setInt(1,userId);
			 ResultSet rset2 = pstmt2.executeQuery();
			 HashMap<Integer,String> desc = new HashMap<Integer,String>();
			 if (rset2.last()) {
					rowCount = rset2.getRow();
					  rset2.beforeFirst(); // not rs.first() because the rs.next() below will move on, missing the first element
			 }
			if(rowCount!=0)
			{
			 while (rset2.next())
			 {
				 int pid =rset2.getInt(1);
				 urls.put(pid,rset2.getString(2));
				 desc.put(pid,rset2.getString(3));
			 }
			 for(int p:urls.keySet())
				{
					ViewedPhotos vp = new ViewedPhotos();
					vp.setPhotoid(p);
					vp.setUrl(urls.get(p));					
					vp.setDesc(desc.get(p));
					vieweds.add(vp);
				}
			}
		}
		 return vieweds;
	}
	
	private String preparePlaceHolders(int length) {
	    StringBuilder builder = new StringBuilder();
	    for (int i = 0; i < length;) {
	        builder.append("?");
	        if (++i < length) {
	            builder.append(",");
	        }
	    }
	    return builder.toString();
	}
	HashMap<Integer,String> friends;
	public Circles getCircles(int userid) throws Exception{
		Circles c = new Circles();
		String circleName ="";
		HashMap<String,HashMap<Integer,String>> hm = new HashMap<String,HashMap<Integer,String>>();
		
		PreparedStatement pstmt3 = conn.prepareStatement("select lastname,firstname,circlename,friendid from "+PennPhotoConstants.schemaName+"users,"+PennPhotoConstants.schemaName+"circle where ownerid =? and friendid=userid order by circlename",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		pstmt3.setInt(1,userid);
		ResultSet rset3 = pstmt3.executeQuery();
		while(rset3.next()){
			if(!rset3.getString(3).equals(circleName)){
				if(!circleName.equals("")){
					hm.put(circleName, friends);
					
				}
				circleName=rset3.getString(3);
				friends = new HashMap<Integer,String> ();
				friends.put(rset3.getInt(4),rset3.getString(1)+" "+ rset3.getString(2));
				
				
			}
			else{
				friends.put(rset3.getInt(4),rset3.getString(1)+" "+ rset3.getString(2));
			}
			
		}
		hm.put(circleName, friends);
		c.setFriends(hm);
		return c;
	}
	
	public String removeFriend(int userid, int friendid,String circlename)throws Exception {
		PreparedStatement pstmt4 = conn.prepareStatement("select c.* from "+PennPhotoConstants.schemaName +"circle c where ownerid =? and friendid = ? and circlename=?",ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		pstmt4.setInt(1,userid);
		pstmt4.setInt(2,friendid);
		pstmt4.setString(3,circlename);
		ResultSet rset4= pstmt4.executeQuery();
		while(rset4.next()){
			rset4.deleteRow();
			return "Success";
		}	
		return "Failure";
		
		
	}
	
	public HashMap<Integer,String> getFriendRecommendation(int userid) throws Exception{
		HashMap<Integer,String> hm = new HashMap<Integer,String>();
		String sql="SELECT userID,firstname,lastname FROM "+PennPhotoConstants.schemaName+"users WHERE userid IN (SELECT distinct F2.userID  FROM  "+PennPhotoConstants.schemaName+"Friends F1, "+PennPhotoConstants.schemaName+"Friends F2, "+PennPhotoConstants.schemaName+"Friends F3 WHERE F1.userID != F2.userID AND F1.friendID = F3.friendID AND F2.friendID = F3.friendID AND  F1.userID = ? UNION SELECT distinct I2.userID FROM  "+PennPhotoConstants.schemaName+"Interests I1, "+PennPhotoConstants.schemaName+"Interests I2, "+PennPhotoConstants.schemaName+"Friends F WHERE F.userID = I1.userID AND   F.friendID != I2.userID AND   I1.userID != I2.userID AND   I1.interestedIn = I2.interestedIn AND   I1.userID = ? UNION SELECT DISTINCT V2.VIEWERID FROM  "+PennPhotoConstants.schemaName+"VIEWED V1, "+PennPhotoConstants.schemaName+"VIEWED V2,"+PennPhotoConstants.schemaName+"Friends F WHERE F.userID = V1.VIEWERID AND   V1.VIEWERID != V2.VIEWERID AND   F.friendID != V2.VIEWERID AND V1.PHOTOID = V2.PHOTOID AND V1.RATE = V2.RATE AND V1.VIEWERID = ?)AND userid NOT IN (Select friendid from "+PennPhotoConstants.schemaName+"friends where userid = ?)";
		System.out.println(sql);
		PreparedStatement pstmt5 = conn.prepareStatement(sql);
		pstmt5.setInt(1,userid);
		pstmt5.setInt(2,userid);
		pstmt5.setInt(3,userid);
		pstmt5.setInt(4, userid);
		ResultSet rset5 = pstmt5.executeQuery();
		while(rset5.next()){
			hm.put(rset5.getInt(1),rset5.getString(2)+" "+ rset5.getString(3));
		}
		return hm;
	}
	
	public String addToCircle(int userid,int friendid, String circleName) throws Exception{
		int circleid=0;
		PreparedStatement pstmt6 = conn.prepareStatement("Insert into "+PennPhotoConstants.schemaName +"friends values(?,?)");
		PreparedStatement pstmt7 = conn.prepareStatement("Insert into "+PennPhotoConstants.schemaName +"friends values(?,?)");
		PreparedStatement pstmt8 = conn.prepareStatement("Insert into "+PennPhotoConstants.schemaName +"circle values(?,?,?,?)");
		PreparedStatement pstmt9 = conn.prepareStatement("Select circleid from "+PennPhotoConstants.schemaName + "circle where ownerid=? and circlename=?");
		pstmt9.setInt(1, userid);
		pstmt9.setString(2,circleName);
		try{
			ResultSet rset9 = pstmt9.executeQuery();
			while(rset9.next()){
				circleid=rset9.getInt(1);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		if (circleid!=0){
		pstmt6.setInt(1, userid);
		pstmt6.setInt(2, friendid);
		pstmt7.setInt(1, friendid);
		pstmt7.setInt(2, userid);
		pstmt8.setInt(1,circleid);
		pstmt8.setString(2,circleName);
		pstmt8.setInt(3, userid);
		pstmt8.setInt(4,friendid);
		try{
			ResultSet rset6 = pstmt6.executeQuery();
			ResultSet rset7 = pstmt7.executeQuery();
			ResultSet rset8 = pstmt8.executeQuery();
		return "Added Successfully!!";
		}
		catch(Exception e){
			
			return "Sorry Cannot add";}
		}
		else{
			PreparedStatement pstmt10 = conn.prepareStatement("Select max(circleid) from "+PennPhotoConstants.schemaName + "circle");
			
			try{
				ResultSet rset10 = pstmt10.executeQuery();
				while(rset10.next()){
					circleid=rset10.getInt(1);
				}
			}
			catch(Exception e){
				e.printStackTrace();
			}
			circleid = circleid+1;
			pstmt6.setInt(1, userid);
			pstmt6.setInt(2, friendid);
			pstmt7.setInt(1, friendid);
			pstmt7.setInt(2, userid);
			pstmt8.setInt(1,circleid);
			pstmt8.setString(2,circleName);
			pstmt8.setInt(3, userid);
			pstmt8.setInt(4,friendid);
			try{
				ResultSet rset6 = pstmt6.executeQuery();
				ResultSet rset7 = pstmt7.executeQuery();
				ResultSet rset8 = pstmt8.executeQuery();
			return "Added Successfully!!";
			}
			catch(Exception e){
				
				return "Sorry Cannot add";}
			
		}
	}
	public String getcircleRecommendation(int userid,int friendid) throws Exception{
		String circleName="";
		PreparedStatement pstmt11 = conn.prepareStatement("SELECT distinct C.circlename FROM  Friends F1, Friends F2, Friends F3,Circle C WHERE F1.userID != F2.userID AND F1.friendID = F3.friendID AND F2.friendID = F3.friendID AND C.FriendID = F3.userID AND C.ownerID = ? AND F2.friendID = ?");
		pstmt11.setInt(1, userid);
		pstmt11.setInt(2, friendid);
		ResultSet rset11 = pstmt11.executeQuery();
		while (rset11.next()){
			circleName=""+rset11.getString(1)+",";
		}
		circleName=circleName.substring(0,circleName.length()-1);
		return circleName;
	}
	
}
	

